package fr.thelem.gestiondemandesthelem.dao.impl;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.apache.log4j.Logger;

import fr.thelem.gestiondemandesthelem.dao.IAppliDao;
import fr.thelem.gestiondemandesthelem.dao.model.AdministrateurEntity;
import fr.thelem.gestiondemandesthelem.dao.model.CDAttributsParametreDemandeEntity;
import fr.thelem.gestiondemandesthelem.dao.model.CDDemandeEntity;
import fr.thelem.gestiondemandesthelem.dao.model.CDEtapeEntity;
import fr.thelem.gestiondemandesthelem.dao.model.CDParametreDemandeEntity;
import fr.thelem.gestiondemandesthelem.dao.model.CDParametreDemandeValeursEntity;
import fr.thelem.gestiondemandesthelem.dao.model.CDParametreEtapeEntity;
import fr.thelem.gestiondemandesthelem.dao.model.CDPointDeControleEntity;
import fr.thelem.gestiondemandesthelem.dao.model.CPDemandeEntity;
import fr.thelem.gestiondemandesthelem.dao.model.CPEtapeEntity;
import fr.thelem.gestiondemandesthelem.dao.model.CPParametreDemandeEntity;
import fr.thelem.gestiondemandesthelem.dao.model.CPParametreEtapeEntity;
import fr.thelem.gestiondemandesthelem.dao.model.CPPointDeControleEntity;
import fr.thelem.gestiondemandesthelem.dao.model.CategorieEntity;
import fr.thelem.gestiondemandesthelem.dao.model.CommentairesEntity;
import fr.thelem.gestiondemandesthelem.dao.model.CorrespondanceParametresEntity;
import fr.thelem.gestiondemandesthelem.dao.model.EtatEntity;
import fr.thelem.gestiondemandesthelem.dao.model.MessageEntity;
import fr.thelem.gestiondemandesthelem.dao.model.PersonneEntity;
import fr.thelem.gestiondemandesthelem.dao.model.SousCategorieEntity;
import fr.thelem.gestiondemandesthelem.dao.model.TypeDemandeEntity;
import fr.thelem.gestiondemandesthelem.dao.model.UtilisateurEntity;

/**
 * implémentation de la DAO AppliDao.
 */
public class AppliDao implements IAppliDao {

    /** Attribut LOGGER */
    private static Logger LOGGER = Logger.getLogger(AppliDao.class);

    @PersistenceContext(unitName = "appliPersistence")
    private EntityManager entityManager;

    /**
     * Setter necessaire pour l'injection Spring.
     * 
     * @param entityManager
     */
    public void setEntityManager(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    public Collection<String> recupererAllCPDemande() {
        Query q = entityManager.createQuery("select titreCPDemande from CPDemandeEntity");
        return q.getResultList();
    }

    public CDParametreEtapeEntity recupererParametreCorrespondantDemande(int CDParametreDemande) {
        CDParametreDemandeEntity cdparam = entityManager.find(CDParametreDemandeEntity.class, CDParametreDemande);
        Query query = entityManager.createQuery("FROM CorrespondanceParametresEntity WHERE CDParametresDemandeCorrespondanceParametre=:Cdparam");
        query.setParameter("Cdparam", cdparam);
        if (query.getResultList().size() > 0) {
            CorrespondanceParametresEntity correspondance = (CorrespondanceParametresEntity) query.getSingleResult();
            correspondance.getCDParametresDemandeCorrespondanceParametre().getCPParametresDemandeCDParametreDemande().size();
            correspondance.getCDParametresDemandeCorrespondanceParametre().getCDDemandesCDParametreDemande().size();
            return correspondance.getCDParametresEtapeCorrespondanceParametre();
        }
        return null;
    }

    public CDParametreDemandeEntity recupererParametreCorrespondantEtape(int CDParametreEtape) {
        CDParametreEtapeEntity cdparametape = entityManager.find(CDParametreEtapeEntity.class, CDParametreEtape);
        Query query = entityManager.createQuery("FROM CorrespondanceParametresEntity WHERE CDParametresEtapeCorrespondanceParametre=:Cdparam");
        query.setParameter("Cdparam", cdparametape);
        if (query.getResultList().size() > 0) {
            CorrespondanceParametresEntity correspondance = (CorrespondanceParametresEntity) query.getSingleResult();
            correspondance.getCDParametresDemandeCorrespondanceParametre().getCPParametresDemandeCDParametreDemande().size();
            correspondance.getCDParametresDemandeCorrespondanceParametre().getCDDemandesCDParametreDemande().size();
            return correspondance.getCDParametresDemandeCorrespondanceParametre();
        }
        return null;
    }

    public List<PersonneEntity> rechercherPersonne(String ldap) {
        List<PersonneEntity> res = new ArrayList<PersonneEntity>();

        Query query = entityManager.createQuery("from PersonneEntity Where ldapPersonne =:param");
        query.setParameter("param", ldap);
       /* if (query.getResultList().size() > 0) {
            CPDemandeEntity cpdemande = (CPDemandeEntity) query.getSingleResult();
            cpdemande.getParametreDemandeCPDemandes().size();
            res.add(cpdemande);
        }*/

        Collection<PersonneEntity> all = entityManager.createQuery("Select d from PersonneEntity d").getResultList();
        for (PersonneEntity c : all) {
            if (c.getLdapPersonne().toLowerCase().contains(ldap.toLowerCase())) {
              //  c.getParametreDemandeCPDemandes().size();
                res.add(c);
            }
        }

        return res;
    }
    
    public PersonneEntity recupererPersonne(String loginLDAP) {
        return entityManager.find(PersonneEntity.class, loginLDAP);
    }

    public AdministrateurEntity recupererAdministrateur(String loginLDAP) {
        return entityManager.find(AdministrateurEntity.class, loginLDAP);
    }

    public UtilisateurEntity recupererUtilisateur(String loginLDAP) {
        return entityManager.find(UtilisateurEntity.class, loginLDAP);
    }

    public void enleverAdmin(String loginLDAP) {
        AdministrateurEntity personne = entityManager.find(AdministrateurEntity.class, loginLDAP);
        entityManager.remove(personne);
    }

    public void enleverUtilisateur(String loginLDAP) {
        UtilisateurEntity personne = entityManager.find(UtilisateurEntity.class, loginLDAP);
        entityManager.remove(personne);
    }

    public void majAdministrateur(String loginLDAP, Date dateMiseAJourPersonne) {
        AdministrateurEntity administrateur = entityManager.find(AdministrateurEntity.class, loginLDAP);
        administrateur.setDateMiseAJourPersonne(dateMiseAJourPersonne);
        entityManager.merge(administrateur);
    }

    public void majUtilisateur(String loginLDAP, Date dateMiseAJourPersonne) {
        UtilisateurEntity utilisateur = entityManager.find(UtilisateurEntity.class, loginLDAP);
        utilisateur.setDateMiseAJourPersonne(dateMiseAJourPersonne);
        entityManager.merge(utilisateur);
    }

    public Collection<AdministrateurEntity> recupererAdministrateurs() {
        Query query = entityManager.createQuery("FROM AdministrateurEntity");
        return query.getResultList();
    }

    public Collection<UtilisateurEntity> recupererUtilisateurs() {
        Query query = entityManager.createQuery("FROM UtilisateurEntity");
        return query.getResultList();
    }

    public void ajouterAdministrateur(String loginLDAP, Date dateCreationPersonne, Date dateMiseAJourPersonne) {
        AdministrateurEntity personne = new AdministrateurEntity();
        personne.setLdapPersonne(loginLDAP);
        personne.setDateCreationPersonne(dateCreationPersonne);
        personne.setDateMiseAJourPersonne(dateMiseAJourPersonne);
        entityManager.persist(personne);
    }

    public void ajouterUtilisateur(String loginLDAP, Date dateCreationPersonne, Date dateMiseAJourPersonne) {
        UtilisateurEntity personne = new UtilisateurEntity();
        personne.setLdapPersonne(loginLDAP);
        personne.setDateCreationPersonne(dateCreationPersonne);
        personne.setDateMiseAJourPersonne(dateMiseAJourPersonne);
        entityManager.persist(personne);
    }

    public boolean isAdministrateurExiste(String ldapPersonne) {
        Query query = entityManager.createQuery("FROM AdministrateurEntity WHERE ldapPersonne=:ldapPersonne");
        query.setParameter("ldapPersonne", ldapPersonne);
        if (query.getResultList().size() > 0) {
            return true;
        }
        return false;
    }

    public boolean isUtilisateurExiste(String ldapPersonne) {
        Query query = entityManager.createQuery("FROM UtilisateurEntity WHERE ldapPersonne=:ldapPersonne");
        query.setParameter("ldapPersonne", ldapPersonne);
        if (query.getResultList().size() > 0) {
            return true;
        }
        return false;
    }

    @Override
    public boolean isTitreExiste(String titre) {
        Query query = entityManager.createQuery("FROM CPDemandeEntity WHERE titreCPDemande=:titre");
        query.setParameter("titre", titre);
        if (query.getResultList().size() == 0) {
            return false;
        } else {
            return true;
        }
    }

    public boolean recupererObligatoire(int idParam, int idDemande) {
        boolean res = true;
        CDParametreDemandeEntity demande = entityManager.find(CDParametreDemandeEntity.class, idParam);
        for (CDAttributsParametreDemandeEntity att : demande.getCDAttributsParametreDemandeCDParametreDemande()) {
            if (att.getCorrespondCDDemandeCDAttributsParametreDemande().getIdentifiantCDDemande() == idDemande) {
                res = att.isObligatoireCDAttributsParametreDemande();
            }
        }
        return res;
    }

    /** Creation d'une demande utilisateur **/

    @Override
    public void ajouterCPDemande(int idCDDemande, String titreDemande, String referenceDemande, Map<String, String> paramsDemande,
            Map<String, String> paramsMulti, String commentaire, PersonneEntity personne) {

    	
        CPDemandeEntity newDemande = new CPDemandeEntity();
        EtatEntity etatDemande = entityManager.find(EtatEntity.class, "Ouverte");
        newDemande.setEtatCPDemande(etatDemande);
        newDemande.setDateCPDemande(new Date());
        newDemande.setTitreCPDemande(titreDemande);
        //newDemande.setCommentaireCPDemande(commentaire);
        ajouterUnCommentaire(commentaire, personne, newDemande);
        newDemande.setReferenceCPDemande(referenceDemande);
        newDemande.setCDDemandeCPDemande(entityManager.find(CDDemandeEntity.class, idCDDemande));
        newDemande.setPersonneEffectueCPDemande(personne);

        entityManager.persist(newDemande);
        ajouterCPPointControle(newDemande.getCDDemandeCPDemande(),newDemande);
        
        Collection<CPParametreDemandeEntity> listeParam = new HashSet<CPParametreDemandeEntity>();
        CPDemandeEntity cpdemande = entityManager.find(CPDemandeEntity.class, newDemande.getIdentifiantCPDemande());

        /** ajout des paramètres à selection simple (input, select, date, liste, fichier) **/
        for (Entry<String, String> entry : paramsDemande.entrySet()) {
            CPParametreDemandeEntity parametreDemande = new CPParametreDemandeEntity();
            parametreDemande.setCDParametresDemandeCPParametreDemande(entityManager.find(CDParametreDemandeEntity.class, Integer.valueOf(entry.getKey())));
            parametreDemande.setCPdemandeCPParametreDemande(cpdemande);
            parametreDemande.setValeurCPParametreDemande(entry.getValue());
            entityManager.persist(parametreDemande);
            listeParam.add(parametreDemande);
            newDemande.setParametreDemandeCPDemandes(listeParam);
        }

        /** ajout des paramètres à selection multiple **/
        for (Entry<String, String> entry : paramsMulti.entrySet()) {
            CDParametreDemandeEntity cdparam = entityManager.find(CDParametreDemandeEntity.class, Integer.valueOf(entry.getKey()));
            String[] res = entry.getValue().split("\\[SEP-\\*\\/]");
            CPParametreDemandeEntity parametreDemandeMulti = new CPParametreDemandeEntity();
            parametreDemandeMulti.setCDParametresDemandeCPParametreDemande(cdparam);
            parametreDemandeMulti.setCPdemandeCPParametreDemande(cpdemande);
            String stringfinal = "";
            for (int i = 0; i < res.length; i++) {
                stringfinal = stringfinal + res[i];
                if (i < res.length - 1) {
                    stringfinal = stringfinal + ",";
                }
            }
            parametreDemandeMulti.setValeurCPParametreDemande(stringfinal);
            entityManager.persist(parametreDemandeMulti);
            listeParam.add(parametreDemandeMulti);
            newDemande.setParametreDemandeCPDemandes(listeParam);
        }
        /** boucle sur toutes les demandes, creation d'une cpetape à chaque cddemande, remplissage des cpparametapes correspondants **/
        for (CDEtapeEntity etape : newDemande.getCDDemandeCPDemande().getCDEtapesCDDemande()) {
            CPEtapeEntity newEtape = new CPEtapeEntity();
            EtatEntity etat = entityManager.find(EtatEntity.class, "Ouverte");
            newEtape.setCDEtapeCPEtape(etape);
            newEtape.setEtatCPEtape(etat);
            entityManager.persist(newEtape);
            for (CPParametreDemandeEntity parametre : newDemande.getParametreDemandeCPDemandes()) {
                CDParametreEtapeEntity cdparametape =
                        recupererParametreCorrespondantDemande(parametre.getCDParametresDemandeCPParametreDemande().getIdentifiantParametreDemande());
                if (cdparametape != null) {
                    if (etape.getCDParametresEtapeCDEtape().contains(cdparametape)) {
                        CPParametreEtapeEntity newParamEtape = new CPParametreEtapeEntity();
                        newParamEtape.setValeurCPParametreEtape(parametre.getValeurCPParametreDemande());
                        newParamEtape.setCDParametreEtape(cdparametape);
                        newParamEtape.setCPEtapeCDParametreEtape(newEtape);
                        newParamEtape.setCPdemandeCPParametreEtape(newDemande);
                        cdparametape.getCPParametreEtapes().add(newParamEtape);
                        entityManager.persist(newParamEtape);
                        entityManager.merge(cdparametape);
                    }
                }

            }

            for (CDParametreEtapeEntity cdparametape : etape.getCDParametresEtapeCDEtape()) {
                for (CorrespondanceParametresEntity corres : cdparametape.getCorrespondanceParametreCDParametreEtapes()) {
                    if (corres.getValeurCDParametreEtape().containsKey(etape)) {
                        CPParametreEtapeEntity newParamEtape = new CPParametreEtapeEntity();
                        newParamEtape.setValeurCPParametreEtape(corres.getValeurCDParametreEtape().get(etape));
                        newParamEtape.setCDParametreEtape(cdparametape);
                        newParamEtape.setCPEtapeCDParametreEtape(newEtape);
                        newParamEtape.setCPdemandeCPParametreEtape(newDemande);
                        cdparametape.getCPParametreEtapes().add(newParamEtape);
                        entityManager.persist(newParamEtape);
                    }
                }
                entityManager.merge(cdparametape);
            }

            
            etape.getCPEtapesCDEtape().add(newEtape);
            newEtape.setCPDemandeCPEtape(newDemande);
            entityManager.merge(newEtape);
            entityManager.merge(etape);
            newDemande.getEtapesCPDemande().add(newEtape);
        }
        
        entityManager.merge(newDemande);

    }
    
    public void ajouterCPPointControle(CDDemandeEntity CDDemande, CPDemandeEntity CPDemande) {
    	
    	LinkedList<CDPointDeControleEntity> ll = recupererCDPointsControle(CDDemande.getIdentifiantCDDemande());
    	
    	
    	for (CDPointDeControleEntity a : ll) {
    		
    		CPPointDeControleEntity cppc = new CPPointDeControleEntity();
    		cppc.setCDPointControleCPPointControle(a);
    		cppc.setCPdemandeCPPointControle(CPDemande);
    		//cppc.setIdentifiantCPPointDeControle(i);
    		
    		a.getCPPointControlesCDPointControle().add(cppc);
    		entityManager.persist(a); // probleme de child (essai)
    		
    		System.out.println("valeur pc : "+cppc.getValeurPointDeControle() +" / id pc (1) : "+ cppc.getIdentifiantCPPointDeControle() +" / nom CDPC : "+cppc.getCDPointControleCPPointControle().getNomCDPointDeControle());
    		
    		entityManager.persist(cppc);
    		
        }
    	
    }

    /** Recuperation des différents paramètres **/

    @Override
    public Collection<CDParametreDemandeEntity> recupererCDParametreDemandes(int idCDDemande) {
        CDDemandeEntity demande = entityManager.find(CDDemandeEntity.class, idCDDemande);
        demande.getCDParametresDemandeCDDemande().size();
        return demande.getCDParametresDemandeCDDemande();
    }

    @Override
    public Collection<CPParametreDemandeEntity> recupererCPParametreDemandes(int idCDParametreDemande) {
        CDParametreDemandeEntity parametreDemande = entityManager.find(CDParametreDemandeEntity.class, idCDParametreDemande);
        parametreDemande.getCPParametresDemandeCDParametreDemande().size();
        return parametreDemande.getCPParametresDemandeCDParametreDemande();
    }

    @Override
    public Collection<CDParametreDemandeValeursEntity> recupererCDParametreDemandesValeurs(int idCDParametreDemande) {
        CDParametreDemandeEntity parametreDemande = entityManager.find(CDParametreDemandeEntity.class, idCDParametreDemande);
        parametreDemande.getCDParametresDemandeValeursCDParametreDemande().size();
        return parametreDemande.getCDParametresDemandeValeursCDParametreDemande();
    }

    @Override
    public Collection<CDPointDeControleEntity> recupererCDPointDeControles(int idCDDemande) {
        CDDemandeEntity demande = entityManager.find(CDDemandeEntity.class, idCDDemande);
        demande.getCDPointsDeControleCDDemande().size();
        return demande.getCDPointsDeControleCDDemande();
    }

    @Override
    public Collection<CPPointDeControleEntity> recupererCPPointDeControles(int idCDPointDeControle) {
        CDPointDeControleEntity pointcontrole = entityManager.find(CDPointDeControleEntity.class, idCDPointDeControle);
        return pointcontrole.getCPPointControlesCDPointControle();
    }

    @Override
    public CDParametreDemandeEntity recupererCDParametreDemande(int idParamDemande) {
        return entityManager.find(CDParametreDemandeEntity.class, idParamDemande);
    }

    /** Gestion des messages **/

    @Override
    public void ajouterMessage(String msg, AdministrateurEntity Administrateur, Date date) {
        MessageEntity message = new MessageEntity();
        message.setContenuMessage(msg);
        message.setDateMessage(new Date());
        message.setAdministrateurMessage(Administrateur);
        entityManager.persist(message);
    }

    @SuppressWarnings("unchecked")
    @Override
    public Collection<MessageEntity> recupererMessages() {
        Query query = entityManager.createQuery("from MessageEntity Order By dateMessage Desc");
        return query.setMaxResults(20).getResultList();
    }

    @Override
    public MessageEntity recupererDernierMessage() {
        Query q = entityManager.createQuery("from MessageEntity where identifiantMessage = (select MAX(identifiantMessage) from MessageEntity)");
        return (MessageEntity) q.getSingleResult();
    }

    @Override
    public void supprimerMessage(int identifiantMessage) {
        Query q = entityManager.createQuery("Delete MessageEntity where identifiantMessage=:id");
        q.setParameter("id", identifiantMessage);
        q.executeUpdate();
    }

    /** Type demande **/

    /**
     * {@inheritDoc}
     */
    @Override
    public int ajouterTypeDemande(String typeDemande) {
        TypeDemandeEntity type = new TypeDemandeEntity();
        type.setNomTypeDemande(typeDemande);
        entityManager.persist(type);
        return type.getIdentifiantTypeDemande();
    }

    /**
     * {@inheritDoc}
     */
    @SuppressWarnings("unchecked")
    @Override
    public Collection<TypeDemandeEntity> recupererTypesDemande() {
        Query q = entityManager.createQuery("from TypeDemandeEntity");
        return q.getResultList();
    }

    /** Catégorie **/

    /**
     * {@inheritDoc}
     */
    @Override
    public void supprimerTypeDemande(int idTypeDemande) {
        Query q = entityManager.createQuery("from TypeDemandeEntity t where t.identifiantTypeDemande=:idTypeDemande");
        q.setParameter("idTypeDemande", idTypeDemande);
        TypeDemandeEntity type = (TypeDemandeEntity) q.getSingleResult();
        entityManager.remove(type);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public int ajouterCategorie(int idTypeDemande, String categorie) {
        CategorieEntity cat = new CategorieEntity();
        cat.setNomCategorie(categorie);
        Query q = entityManager.createQuery("from TypeDemandeEntity t where t.identifiantTypeDemande=:idTypeDemande");
        q.setParameter("idTypeDemande", idTypeDemande);
        TypeDemandeEntity type = (TypeDemandeEntity) q.getSingleResult();
        type.getCategoriesTypeDemande().add(cat);
        cat.setTypeDemandeCategorie(type);
        entityManager.persist(type);
        return cat.getIdentifiantCategorie();

    }

    /**
     * {@inheritDoc}
     */
    @SuppressWarnings("unchecked")
    @Override
    public Collection<CategorieEntity> recupererCategories(int idTypeDemande) {
        Query q = entityManager.createQuery("from CategorieEntity c where c.typeDemandeCategorie.identifiantTypeDemande=:idTypeDemande");
        q.setParameter("idTypeDemande", idTypeDemande);
        return q.getResultList();
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void supprimerCategorie(int idCategorie) {
        Query q = entityManager.createQuery("from CategorieEntity c where c.identifiantCategorie=:idCategorie");
        q.setParameter("idCategorie", idCategorie);
        CategorieEntity cat = (CategorieEntity) q.getSingleResult();
        entityManager.remove(cat);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public int ajouterSousCategorie(int idCategorie, String sousCategorie) {
        SousCategorieEntity sousCat = new SousCategorieEntity();
        sousCat.setNomSousCategorie(sousCategorie);
        Query q = entityManager.createQuery("from CategorieEntity c where c.identifiantCategorie=:idCategorie");
        q.setParameter("idCategorie", idCategorie);
        CategorieEntity cat = (CategorieEntity) q.getSingleResult();
        cat.getSousCategoriesCategorie().add(sousCat);
        sousCat.setCategorieSousCategorie(cat);
        entityManager.persist(cat);
        return sousCat.getIdentifiantSousCategorie();
    }

    /**
     * {@inheritDoc}
     */
    @SuppressWarnings("unchecked")
    @Override
    public Collection<SousCategorieEntity> recupererSousCategories(int idCategorie) {
        Query q = entityManager.createQuery("from SousCategorieEntity sc where sc.categorieSousCategorie.identifiantCategorie=:idCategorie");
        q.setParameter("idCategorie", idCategorie);
        return q.getResultList();
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void supprimerSousCategorie(int idSousCategorie) {
        Query q = entityManager.createQuery("from SousCategorieEntity sc where sc.identifiantSousCategorie=:idSousCategorie");
        q.setParameter("idSousCategorie", idSousCategorie);
        SousCategorieEntity sousCat = (SousCategorieEntity) q.getSingleResult();
        entityManager.remove(sousCat);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public int ajouterCDDemande(int idTypeDemande, int idSousCategorie, String CDDemande) {
        CDDemandeEntity dem = new CDDemandeEntity();
        dem.setNomCDDemande(CDDemande);
        Query q = entityManager.createQuery("from SousCategorieEntity sc where sc.identifiantSousCategorie=:idSousCategorie");
        q.setParameter("idSousCategorie", idSousCategorie);
        SousCategorieEntity sousCat = (SousCategorieEntity) q.getSingleResult();
        sousCat.getCDDemandeSousCategorie().add(dem);
        Query q2 = entityManager.createQuery("from TypeDemandeEntity t where t.identifiantTypeDemande=:idTypeDemande");
        q2.setParameter("idTypeDemande", idTypeDemande);
        TypeDemandeEntity typeDem = (TypeDemandeEntity) q2.getSingleResult();
        typeDem.getCDDemandesTypeDemande().add(dem);
        dem.setSousCategorieCDDemande(sousCat);
        dem.setTypeDemandeCDDemande(typeDem);
        entityManager.persist(typeDem);
        entityManager.persist(sousCat);
        return dem.getIdentifiantCDDemande();
    }

    /**
     * {@inheritDoc}
     */
    @SuppressWarnings("unchecked")
    @Override
    public Collection<CDDemandeEntity> recupererCDDemandes(int idSousCategorie) {
        Query q = entityManager.createQuery("from CDDemandeEntity d where d.sousCategorieCDDemande.identifiantSousCategorie=:idSousCategorie");
        q.setParameter("idSousCategorie", idSousCategorie);
        return q.getResultList();
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public CDDemandeEntity recupererCDDemande(int idCDDemande) {
        Query q = entityManager.createQuery("from CDDemandeEntity d where d.identifiantCDDemande=:idCDDemande");
        q.setParameter("idCDDemande", idCDDemande);
        return (CDDemandeEntity) q.getSingleResult();
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void supprimerCDDemande(int idCDDemande) {
        Query q = entityManager.createQuery("from CDDemandeEntity d where d.identifiantCDDemande=:idCDDemande");
        q.setParameter("idCDDemande", idCDDemande);
        CDDemandeEntity demAdmin = (CDDemandeEntity) q.getSingleResult();
        entityManager.remove(demAdmin);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public int ajouterCDEtape(int idCDDemande, String CDEtape, String descriptionCDEtape) {
        CDEtapeEntity etapeCD = new CDEtapeEntity();
        etapeCD.setNomCDEtape(CDEtape);
        etapeCD.setDescriptionCDEtape(descriptionCDEtape);
        Query q = entityManager.createQuery("from CDDemandeEntity da where da.identifiantCDDemande=:idCDDemande");
        q.setParameter("idCDDemande", idCDDemande);
        CDDemandeEntity dem = (CDDemandeEntity) q.getSingleResult();
        dem.getCDEtapesCDDemande().add(etapeCD);
        etapeCD.getCDDemandesCDEtape().add(dem);
        entityManager.persist(etapeCD);
        return etapeCD.getIdentifiantCDEtape();
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public String ajouterSelectionCDEtape(int idCDDemande, int idCDEtape) {
        Query q = entityManager.createQuery("from CDEtapeEntity e where e.identifiantCDEtape=:idCDEtape");
        q.setParameter("idCDEtape", idCDEtape);
        CDEtapeEntity etape = (CDEtapeEntity) q.getSingleResult();
        Query q2 = entityManager.createQuery("from CDDemandeEntity da where da.identifiantCDDemande=:idCDDemande");
        q2.setParameter("idCDDemande", idCDDemande);
        CDDemandeEntity dem = (CDDemandeEntity) q2.getSingleResult();
        dem.getCDEtapesCDDemande().add(etape);
        etape.getCDDemandesCDEtape().add(dem);
        entityManager.merge(etape);
        return etape.getNomCDEtape();
    }

    /**
     * {@inheritDoc}
     */
    @SuppressWarnings("unchecked")
    @Override
    public Collection<CDEtapeEntity> recupererCDEtapes(int idCDDemande) {
        Query q = entityManager.createQuery("Select CDEtapesCDDemande from CDDemandeEntity d where d.identifiantCDDemande=:idCDDemande");
        q.setParameter("idCDDemande", idCDDemande);
        return q.getResultList();
    }

    /**
     * {@inheritDoc}
     */
    @SuppressWarnings({"unchecked"})
    @Override
    public Collection<CDEtapeEntity> recupererToutesCDEtapes(int idCDDemande) {
        Query q = entityManager.createQuery("Select CDEtapesCDDemande from CDDemandeEntity d where d.identifiantCDDemande=:idCDDemande");
        q.setParameter("idCDDemande", idCDDemande);
        Collection<CDEtapeEntity> colEtapes = q.getResultList();
        if (colEtapes.size() == 0) {
            Query q2 = entityManager.createQuery("From CDEtapeEntity");
            return q2.getResultList();
        } else {
            Query q2 = entityManager.createQuery("From CDEtapeEntity e where e not in (:colEtapes)");
            q2.setParameter("colEtapes", colEtapes);
            return q2.getResultList();
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public String recupererDescriptionCDEtape(int idCDEtape) {
        Query q = entityManager.createQuery("select descriptionCDEtape from CDEtapeEntity e where e.identifiantCDEtape=:idCDEtape");
        q.setParameter("idCDEtape", idCDEtape);
        return q.getSingleResult().toString();
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void modifierDescriptionEtape(int idCDEtape, String descriptionCDEtape) {
        Query q = entityManager.createQuery("from CDEtapeEntity e where e.identifiantCDEtape=:idCDEtape");
        q.setParameter("idCDEtape", idCDEtape);
        CDEtapeEntity etape = (CDEtapeEntity) q.getSingleResult();
        etape.setDescriptionCDEtape(descriptionCDEtape);
        entityManager.merge(etape);
    }

    /**
     * {@inheritDoc}
     */
    @SuppressWarnings("unchecked")
    @Override
    public void supprimerCDEtape(int idCDEtape, int idCDDemande) {
        Query q = entityManager.createQuery("from CDEtapeEntity e where e.identifiantCDEtape=:idCDEtape");
        q.setParameter("idCDEtape", idCDEtape);
        CDEtapeEntity etape = (CDEtapeEntity) q.getSingleResult();
        Query q2 = entityManager.createQuery("from CDDemandeEntity da where da.identifiantCDDemande=:idCDDemande");
        q2.setParameter("idCDDemande", idCDDemande);
        CDDemandeEntity dem = (CDDemandeEntity) q2.getSingleResult();
        dem.getCDEtapesCDDemande().remove(etape);
        etape.getCDDemandesCDEtape().remove(dem);
        entityManager.merge(etape);
    }

    /**
     * {@inheritDoc}
     */
    @SuppressWarnings("unchecked")
    @Override
    public void supprimerDefinitivementCDEtape(int idCDEtape) {
        Query q = entityManager.createQuery("from CDEtapeEntity e where e.identifiantCDEtape=:idCDEtape");
        q.setParameter("idCDEtape", idCDEtape);
        CDEtapeEntity etape = (CDEtapeEntity) q.getSingleResult();
        if (etape.getCDDemandesCDEtape().size() != 0) {
            for (CDDemandeEntity dem : etape.getCDDemandesCDEtape()) {
                dem.getCDEtapesCDDemande().remove(etape);
            }
        }
        if (etape.getCDParametresEtapeCDEtape().size() != 0) {
            for (CDParametreEtapeEntity paramEtape : etape.getCDParametresEtapeCDEtape()) {
                paramEtape.getCDEtapesCDParametreEtape().remove(etape);
                if (paramEtape.getTypeSaisieCDParametreEtape().equals("admin")) {
                    for (CorrespondanceParametresEntity cor : paramEtape.getCorrespondanceParametreCDParametreEtapes()) {
                        if (cor.getValeurCDParametreEtape().containsKey(etape)) {
                            cor.getValeurCDParametreEtape().remove(etape);
                        }
                    }
                }
            }
        }
        if (etape.getCPEtapesCDEtape().size() != 0) {
            for (CPEtapeEntity CPEtape : etape.getCPEtapesCDEtape()) {
                CPEtape.setCDEtapeCPEtape(null);
            }
        }
        entityManager.remove(etape);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public int ajouterCDPointControle(int idCDDemande, String CDPointControle, String typeSaisieCDPointControle) {
        Query q = entityManager.createQuery("from CDDemandeEntity da where da.identifiantCDDemande=:idCDDemande");
        q.setParameter("idCDDemande", idCDDemande);
        CDDemandeEntity dem = (CDDemandeEntity) q.getSingleResult();
        Query q2 = entityManager.createQuery("Select CDPointsDeControleCDDemande from CDDemandeEntity d where d.identifiantCDDemande=:idCDDemande");
        q2.setParameter("idCDDemande", idCDDemande);
        Collection<CDPointDeControleEntity> colPointsControles = q2.getResultList();
        CDPointDeControleEntity pointCD = new CDPointDeControleEntity();
        pointCD.setNomCDPointDeControle(CDPointControle);
        int cpt = 0;
        for (CDPointDeControleEntity pc : colPointsControles) {
            if (pc.getNumeroCDPointDeControle().get(dem) > cpt) {
                cpt = pc.getNumeroCDPointDeControle().get(dem);
            }
        }
       
        pointCD.getNumeroCDPointDeControle().put(dem, cpt + 1);
      //  CPPointDeControleEntity pointCP = new CPPointDeControleEntity();
        if (typeSaisieCDPointControle.equals("Case à cocher")) {
            pointCD.setTypeSaisieCDPointDeControle("case");
      //      pointCP.setValeurPointDeControle("false");
        } else {
            pointCD.setTypeSaisieCDPointDeControle("input");
     //       pointCP.setValeurPointDeControle("");
        }
     //   pointCP.setCDPointControleCPPointControle(pointCD);
       // pointCD.setCPPointControlesCDPointControle(pointCP);
        dem.getCDPointsDeControleCDDemande().add(pointCD);
        pointCD.getCDDemandesCDPointControle().add(dem);
      //  entityManager.persist(pointCP);
        entityManager.persist(pointCD);
        return pointCD.getIdentifiantCDPointDeControle();
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public String ajouterSelectionCDPointControle(int idCDDemande, int idCDPointControle) {
        Query q = entityManager.createQuery("from CDPointDeControleEntity pc where pc.identifiantCDPointDeControle=:idCDPointControle");
        q.setParameter("idCDPointControle", idCDPointControle);
        CDPointDeControleEntity pointControle = (CDPointDeControleEntity) q.getSingleResult();
        Query q2 = entityManager.createQuery("from CDDemandeEntity da where da.identifiantCDDemande=:idCDDemande");
        q2.setParameter("idCDDemande", idCDDemande);
        CDDemandeEntity dem = (CDDemandeEntity) q2.getSingleResult();
        dem.getCDPointsDeControleCDDemande().add(pointControle);
        Query q3 = entityManager.createQuery("Select CDPointsDeControleCDDemande from CDDemandeEntity d where d.identifiantCDDemande=:idCDDemande");
        q3.setParameter("idCDDemande", idCDDemande);
        Collection<CDPointDeControleEntity> colPointsControles = q3.getResultList();
        int cpt = 0;
        for (CDPointDeControleEntity pc : colPointsControles) {
            if (pc.getIdentifiantCDPointDeControle() != idCDPointControle) {
                if (pc.getNumeroCDPointDeControle().get(dem) > cpt) {
                    cpt = pc.getNumeroCDPointDeControle().get(dem);
                }
            }
        }
        pointControle.getNumeroCDPointDeControle().put(dem, cpt + 1);
        pointControle.getCDDemandesCDPointControle().add(dem);
        entityManager.merge(pointControle);
        return pointControle.getNomCDPointDeControle();
    }

    /**
     * {@inheritDoc}
     */
    @SuppressWarnings("unchecked")
    @Override
    public LinkedList<CDPointDeControleEntity> recupererCDPointsControle(int idCDDemande) {
        Query q = entityManager.createQuery("Select CDPointsDeControleCDDemande from CDDemandeEntity d where d.identifiantCDDemande=:idCDDemande");
        q.setParameter("idCDDemande", idCDDemande);
        Collection<CDPointDeControleEntity> colPointsControles = q.getResultList();
        Query q2 = entityManager.createQuery("from CDDemandeEntity da where da.identifiantCDDemande=:idCDDemande");
        q2.setParameter("idCDDemande", idCDDemande);
        CDDemandeEntity dem = (CDDemandeEntity) q2.getSingleResult();
        LinkedList<CDPointDeControleEntity> triNumerosPointsControles = new LinkedList<CDPointDeControleEntity>();
        int cpt = 1;
        while (triNumerosPointsControles.size() < colPointsControles.size()) {
            for (CDPointDeControleEntity pc : colPointsControles) {
                if (pc.getNumeroCDPointDeControle().get(dem) == cpt) {
                    triNumerosPointsControles.add(pc);
                    cpt++;
                }
            }
        }
        return triNumerosPointsControles;
    }

    /**
     * {@inheritDoc}
     */
    @SuppressWarnings("unchecked")
    @Override
    public Collection<CDPointDeControleEntity> recupererTousCDPointsControle(int idCDDemande) {
        Query q = entityManager.createQuery("Select CDPointsDeControleCDDemande from CDDemandeEntity d where d.identifiantCDDemande=:idCDDemande");
        q.setParameter("idCDDemande", idCDDemande);
        Collection<CDPointDeControleEntity> colPointsControles = q.getResultList();
        if (colPointsControles.size() == 0) {
            Query q2 = entityManager.createQuery("From CDPointDeControleEntity");
            return q2.getResultList();
        } else {
            Query q2 = entityManager.createQuery("From CDPointDeControleEntity pc where pc not in (:colPointsControles)");
            q2.setParameter("colPointsControles", colPointsControles);
            return q2.getResultList();
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public int recupererNumeroCDPointControle(int idCDPointControle, int idCDDemande) {
        Query q = entityManager.createQuery("from CDDemandeEntity da where da.identifiantCDDemande=:idCDDemande");
        q.setParameter("idCDDemande", idCDDemande);
        CDDemandeEntity dem = (CDDemandeEntity) q.getSingleResult();
        Query q2 = entityManager.createQuery("from CDPointDeControleEntity pc where pc.identifiantCDPointDeControle=:idCDPointControle");
        q2.setParameter("idCDPointControle", idCDPointControle);
        CDPointDeControleEntity pointControle = (CDPointDeControleEntity) q2.getSingleResult();
        return pointControle.getNumeroCDPointDeControle().get(dem);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void modifierNumeroCDPointControle(int idCDPointControle, int idCDDemande, int numeroCDPointControle) {
        Query q = entityManager.createQuery("from CDDemandeEntity da where da.identifiantCDDemande=:idCDDemande");
        q.setParameter("idCDDemande", idCDDemande);
        CDDemandeEntity dem = (CDDemandeEntity) q.getSingleResult();
        Query q2 = entityManager.createQuery("from CDPointDeControleEntity pc where pc.identifiantCDPointDeControle=:idCDPointControle");
        q2.setParameter("idCDPointControle", idCDPointControle);
        CDPointDeControleEntity pointControle = (CDPointDeControleEntity) q2.getSingleResult();
        int oldPosition = pointControle.getNumeroCDPointDeControle().get(dem);
        if (pointControle.getNumeroCDPointDeControle().containsKey(dem)) {
            pointControle.getNumeroCDPointDeControle().remove(dem);
        }
        Query q3 = entityManager.createQuery("Select CDPointsDeControleCDDemande from CDDemandeEntity d where d.identifiantCDDemande=:idCDDemande");
        q3.setParameter("idCDDemande", idCDDemande);
        Collection<CDPointDeControleEntity> colPointsControles = q3.getResultList();
        for (CDPointDeControleEntity pointCD : colPointsControles) {
            if (pointCD.getIdentifiantCDPointDeControle() != idCDPointControle) {
                if (pointCD.getNumeroCDPointDeControle().get(dem) < oldPosition && pointCD.getNumeroCDPointDeControle().get(dem) >= numeroCDPointControle) {
                    int anciennePosition = pointCD.getNumeroCDPointDeControle().get(dem);
                    pointCD.getNumeroCDPointDeControle().remove(dem);
                    pointCD.getNumeroCDPointDeControle().put(dem, anciennePosition + 1);
                    entityManager.merge(pointCD);
                } else if (pointCD.getNumeroCDPointDeControle().get(dem) > oldPosition
                        && pointCD.getNumeroCDPointDeControle().get(dem) <= numeroCDPointControle) {
                    int anciennePosition = pointCD.getNumeroCDPointDeControle().get(dem);
                    pointCD.getNumeroCDPointDeControle().remove(dem);
                    pointCD.getNumeroCDPointDeControle().put(dem, anciennePosition - 1);
                    entityManager.merge(pointCD);
                }
            }
        }
        pointControle.getNumeroCDPointDeControle().put(dem, numeroCDPointControle);
        entityManager.merge(pointControle);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public boolean testerNumeroCDPointControle(int idCDDemande, int numeroCDPointControle) {
        Query q = entityManager.createQuery("from CDDemandeEntity da where da.identifiantCDDemande=:idCDDemande");
        q.setParameter("idCDDemande", idCDDemande);
        CDDemandeEntity dem = (CDDemandeEntity) q.getSingleResult();
        Query q2 = entityManager.createQuery("Select CDPointsDeControleCDDemande from CDDemandeEntity d where d.identifiantCDDemande=:idCDDemande");
        q2.setParameter("idCDDemande", idCDDemande);
        Collection<CDPointDeControleEntity> colPointsControles = q2.getResultList();
        int cpt = 1;
        for (CDPointDeControleEntity pointCD : colPointsControles) {
            if (cpt < pointCD.getNumeroCDPointDeControle().get(dem)) {
                cpt = pointCD.getNumeroCDPointDeControle().get(dem);
            }
        }
        if (cpt < numeroCDPointControle) {
            return false;
        }
        return true;
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void supprimerCDPointControle(int idCDPointControle, int idCDDemande) {
        Query q = entityManager.createQuery("from CDPointDeControleEntity pc where pc.identifiantCDPointDeControle=:idCDPointControle");
        q.setParameter("idCDPointControle", idCDPointControle);
        CDPointDeControleEntity pointControle = (CDPointDeControleEntity) q.getSingleResult();
        Query q2 = entityManager.createQuery("from CDDemandeEntity da where da.identifiantCDDemande=:idCDDemande");
        q2.setParameter("idCDDemande", idCDDemande);
        CDDemandeEntity dem = (CDDemandeEntity) q2.getSingleResult();
        Query q3 = entityManager.createQuery("Select CDPointsDeControleCDDemande from CDDemandeEntity d where d.identifiantCDDemande=:idCDDemande");
        q3.setParameter("idCDDemande", idCDDemande);
        Collection<CDPointDeControleEntity> colPointsControles = q3.getResultList();
        int numeroCDPointControle = pointControle.getNumeroCDPointDeControle().get(dem);
        for (CDPointDeControleEntity pointCD : colPointsControles) {
            if (pointCD.getIdentifiantCDPointDeControle() != idCDPointControle) {
                if (pointCD.getNumeroCDPointDeControle().get(dem) > numeroCDPointControle) {
                    int anciennePosition = pointCD.getNumeroCDPointDeControle().get(dem);
                    pointCD.getNumeroCDPointDeControle().remove(dem);
                    pointCD.getNumeroCDPointDeControle().put(dem, anciennePosition - 1);
                    entityManager.merge(pointCD);
                }
            }
        }
        dem.getCDPointsDeControleCDDemande().remove(pointControle);
        pointControle.getNumeroCDPointDeControle().remove(dem);
        pointControle.getCDDemandesCDPointControle().remove(dem);
        entityManager.merge(pointControle);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void supprimerDefinitivementCDPointControle(int idCDPointControle) {
        Query q = entityManager.createQuery("from CDPointDeControleEntity pc where pc.identifiantCDPointDeControle=:idCDPointControle");
        q.setParameter("idCDPointControle", idCDPointControle);
        CDPointDeControleEntity pointControle = (CDPointDeControleEntity) q.getSingleResult();
        if (pointControle.getCDDemandesCDPointControle().size() != 0) {
            for (CDDemandeEntity dem : pointControle.getCDDemandesCDPointControle()) {
                for (CDPointDeControleEntity pointCD : dem.getCDPointsDeControleCDDemande()) {
                    if (pointCD.getIdentifiantCDPointDeControle() != idCDPointControle) {
                        if (pointCD.getNumeroCDPointDeControle().get(dem) > pointControle.getNumeroCDPointDeControle().get(dem)) {
                            int anciennePosition = pointCD.getNumeroCDPointDeControle().get(dem);
                            pointCD.getNumeroCDPointDeControle().remove(dem);
                            pointCD.getNumeroCDPointDeControle().put(dem, anciennePosition - 1);
                            entityManager.merge(pointCD);
                        }
                    }
                }
                dem.getCDPointsDeControleCDDemande().remove(pointControle);
            }
        }
        for (CPPointDeControleEntity cppointControle : pointControle.getCPPointControlesCDPointControle()){
        	cppointControle.setCDPointControleCPPointControle(null);
        }
        
        entityManager.remove(pointControle);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public int ajouterCDParametreDemande(int idCDDemande, String CDParametreDemande, boolean obligation, String typeSaisieCDParametreDemande,
            String descriptionCDParametreDemande, String valeursCDParametreDemande) {
        Query q = entityManager.createQuery("from CDDemandeEntity da where da.identifiantCDDemande=:idCDDemande");
        q.setParameter("idCDDemande", idCDDemande);
        CDDemandeEntity dem = (CDDemandeEntity) q.getSingleResult();
        CDParametreDemandeEntity paramDem = new CDParametreDemandeEntity();
        paramDem.setNomParametreDemande(CDParametreDemande);
        CDAttributsParametreDemandeEntity attributs = new CDAttributsParametreDemandeEntity();
        attributs.setCorrespondCDDemandeCDAttributsParametreDemande(dem);
        attributs.setCDParametreDemandeCDAttributsParametreDemande(paramDem);
        attributs.setDescriptionCDAttributsParametreDemande(descriptionCDParametreDemande);
        attributs.setObligatoireCDAttributsParametreDemande(obligation);
        entityManager.persist(attributs);
        paramDem.getCDAttributsParametreDemandeCDParametreDemande().add(attributs);
        paramDem.setTypeSaisieParametreDemande(typeSaisieCDParametreDemande);
        dem.getCDParametresDemandeCDDemande().add(paramDem);
        paramDem.getCDDemandesCDParametreDemande().add(dem);
        if (!valeursCDParametreDemande.equals("")) {
            String[] res = valeursCDParametreDemande.split("\\[SEP-\\*\\/]");
            for (int i = 0; i < res.length; i++) {
                CDParametreDemandeValeursEntity paramDemVal = new CDParametreDemandeValeursEntity();
                paramDemVal.setValeurCDParametreDemandeValeurs(res[i]);
                paramDem.getCDParametresDemandeValeursCDParametreDemande().add(paramDemVal);
                paramDemVal.setCDParametreDemandeCDParametreDemandeValeurs(paramDem);
            }
        }
        entityManager.persist(paramDem);
        return paramDem.getIdentifiantParametreDemande();
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public String ajouterSelectionCDParametreDemande(int idCDDemande, int idCDParametreDemande, boolean obligation, String descriptionCDParametreDemande) {
        Query q = entityManager.createQuery("from CDParametreDemandeEntity pd where pd.identifiantParametreDemande=:idCDParametreDemande");
        q.setParameter("idCDParametreDemande", idCDParametreDemande);
        CDParametreDemandeEntity paramDem = (CDParametreDemandeEntity) q.getSingleResult();
        Query q2 = entityManager.createQuery("from CDDemandeEntity da where da.identifiantCDDemande=:idCDDemande");
        q2.setParameter("idCDDemande", idCDDemande);
        CDDemandeEntity dem = (CDDemandeEntity) q2.getSingleResult();
        dem.getCDParametresDemandeCDDemande().add(paramDem);
        paramDem.getCDDemandesCDParametreDemande().add(dem);
        CDAttributsParametreDemandeEntity attributs = new CDAttributsParametreDemandeEntity();
        attributs.setCorrespondCDDemandeCDAttributsParametreDemande(dem);
        attributs.setCDParametreDemandeCDAttributsParametreDemande(paramDem);
        attributs.setDescriptionCDAttributsParametreDemande(descriptionCDParametreDemande);
        attributs.setObligatoireCDAttributsParametreDemande(obligation);
        entityManager.persist(attributs);
        paramDem.getCDAttributsParametreDemandeCDParametreDemande().add(attributs);
        entityManager.merge(paramDem);
        return paramDem.getNomParametreDemande();
    }

    /**
     * {@inheritDoc}
     */
    @SuppressWarnings("unchecked")
    @Override
    public Collection<CDParametreDemandeEntity> recupererCDParametresDemande(int idCDDemande) {
        Query q = entityManager.createQuery("Select CDParametresDemandeCDDemande from CDDemandeEntity d where d.identifiantCDDemande=:idCDDemande");
        q.setParameter("idCDDemande", idCDDemande);
        return q.getResultList();
    }

    /**
     * {@inheritDoc}
     */
    @SuppressWarnings("unchecked")
    @Override
    public Collection<CDParametreDemandeEntity> recupererTousCDParametresDemande(int idCDDemande) {
        Query q = entityManager.createQuery("Select CDParametresDemandeCDDemande from CDDemandeEntity d where d.identifiantCDDemande=:idCDDemande");
        q.setParameter("idCDDemande", idCDDemande);
        Collection<CDParametreDemandeEntity> colParametresDemande = q.getResultList();
        if (colParametresDemande.size() == 0) {
            Query q2 = entityManager.createQuery("From CDParametreDemandeEntity");
            return q2.getResultList();
        } else {
            Query q2 = entityManager.createQuery("From CDParametreDemandeEntity pd where pd not in (:colParametresDemande)");
            q2.setParameter("colParametresDemande", colParametresDemande);
            return q2.getResultList();
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public String recupererDescriptionCDParametreDemande(int idCDDemande, int idCDParametreDemande) {
        Query q = entityManager.createQuery("from CDParametreDemandeEntity pd where pd.identifiantParametreDemande=:idCDParametreDemande");
        q.setParameter("idCDParametreDemande", idCDParametreDemande);
        CDParametreDemandeEntity paramDem = (CDParametreDemandeEntity) q.getSingleResult();
        Query q2 = entityManager.createQuery("from CDDemandeEntity da where da.identifiantCDDemande=:idCDDemande");
        q2.setParameter("idCDDemande", idCDDemande);
        CDDemandeEntity dem = (CDDemandeEntity) q2.getSingleResult();
        for (CDAttributsParametreDemandeEntity attribut : paramDem.getCDAttributsParametreDemandeCDParametreDemande()) {
            if (attribut.getCorrespondCDDemandeCDAttributsParametreDemande().equals(dem)) {
                return attribut.getDescriptionCDAttributsParametreDemande();
            }
        }
        return "";
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public String recupererTypeSaisieCDParametreDemande(int idCDParametreDemande) {
        Query q =
                entityManager
                        .createQuery("select typeSaisieParametreDemande from CDParametreDemandeEntity pd where pd.identifiantParametreDemande=:idCDParametreDemande");
        q.setParameter("idCDParametreDemande", idCDParametreDemande);
        return q.getSingleResult().toString();
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void modifierDescriptionCDParametreDemande(int idCDDemande, int idCDParametreDemande, String descriptionCDParametreDemande) {
        Query q = entityManager.createQuery("from CDParametreDemandeEntity pd where pd.identifiantParametreDemande=:idCDParametreDemande");
        q.setParameter("idCDParametreDemande", idCDParametreDemande);
        CDParametreDemandeEntity paramDem = (CDParametreDemandeEntity) q.getSingleResult();
        Query q2 = entityManager.createQuery("from CDDemandeEntity da where da.identifiantCDDemande=:idCDDemande");
        q2.setParameter("idCDDemande", idCDDemande);
        CDDemandeEntity dem = (CDDemandeEntity) q2.getSingleResult();
        for (CDAttributsParametreDemandeEntity attribut : paramDem.getCDAttributsParametreDemandeCDParametreDemande()) {
            if (attribut.getCorrespondCDDemandeCDAttributsParametreDemande().equals(dem)) {
                attribut.setDescriptionCDAttributsParametreDemande(descriptionCDParametreDemande);
                entityManager.merge(attribut);
            }
        }
        entityManager.merge(paramDem);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void modifierCPDemande(int idLaDemande, String NomDemande, String CodeProjetDemande, String ChargeDemande,
            String ReferenceDemande) {

        Query q = entityManager.createQuery("from CPDemandeEntity d where d.identifiantCPDemande=:idLaDemande");
        q.setParameter("idLaDemande", idLaDemande);
        CPDemandeEntity demande = (CPDemandeEntity) q.getSingleResult();
        demande.setTitreCPDemande(NomDemande);
        demande.setCodeProjetCPDemande(CodeProjetDemande);
        demande.setChargeCPDemande(ChargeDemande);
        demande.setReferenceCPDemande(ReferenceDemande);

        entityManager.merge(demande);

    }
    
    public void modifierCPParametreValeur(int idCPDemande, String idCDParametreDemande, String lesParams) {
    	
    	if (lesParams == null) {
    		lesParams = "";
    	}
    	String[] paramString = lesParams.split(", ");
    	
    	if (idCDParametreDemande==null) {
    		idCDParametreDemande ="";
    	}
        String[] idparamString = idCDParametreDemande.split(", ");

        if (lesParams != "" || idCDParametreDemande!="") {
        	
	        CPParametreDemandeEntity paramVal = null;
	        
	        for (int i=0; i<idparamString.length;i++) {
	        	
	        	Query q = entityManager.createQuery("from CPParametreDemandeEntity p where p.identifiantCPParametreDemande=:param");
	        	q.setParameter("param", Integer.parseInt(idparamString[i]));
	        	paramVal = (CPParametreDemandeEntity) q.getSingleResult();
	        	paramVal.setValeurCPParametreDemande(paramString[i]);
	        	
	        }
	        
	        System.out.println("yooooooooo "+paramVal.getCPdemandeCPParametreDemande().getIdentifiantCPDemande());
	        
	        
	
	        entityManager.merge(paramVal);
        }
        
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void supprimerCDParametreDemande(int idCDParametreDemande, int idCDDemande) {
        Query q = entityManager.createQuery("from CDParametreDemandeEntity pd where pd.identifiantParametreDemande=:idCDParametreDemande");
        q.setParameter("idCDParametreDemande", idCDParametreDemande);
        CDParametreDemandeEntity paramDem = (CDParametreDemandeEntity) q.getSingleResult();
        Query q2 = entityManager.createQuery("from CDDemandeEntity da where da.identifiantCDDemande=:idCDDemande");
        q2.setParameter("idCDDemande", idCDDemande);
        CDDemandeEntity dem = (CDDemandeEntity) q2.getSingleResult();
        dem.getCDParametresDemandeCDDemande().remove(paramDem);
        paramDem.getCDDemandesCDParametreDemande().remove(dem);
        for (CDAttributsParametreDemandeEntity attribut : paramDem.getCDAttributsParametreDemandeCDParametreDemande()) {
            if (attribut.getCorrespondCDDemandeCDAttributsParametreDemande().equals(dem)) {
                entityManager.remove(attribut);
            }
        }
        paramDem.setCDAttributsParametreDemandeCDParametreDemande(null);
        entityManager.merge(paramDem);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void supprimerDefinitivementCDParametreDemande(int idCDParametreDemande) {
        Query q = entityManager.createQuery("from CDParametreDemandeEntity pd where pd.identifiantParametreDemande=:idCDParametreDemande");
        q.setParameter("idCDParametreDemande", idCDParametreDemande);
        CDParametreDemandeEntity paramDem = (CDParametreDemandeEntity) q.getSingleResult();
        if (paramDem.getCDDemandesCDParametreDemande().size() != 0) {
            for (CDDemandeEntity dem : paramDem.getCDDemandesCDParametreDemande()) {
                dem.getCDParametresDemandeCDDemande().remove(paramDem);
            }
        }
        if (paramDem.getCDParametresDemandeValeursCDParametreDemande().size() != 0) {
            for (CDParametreDemandeValeursEntity paramDemVal : paramDem.getCDParametresDemandeValeursCDParametreDemande()) {
                paramDemVal.setCDParametreDemandeCDParametreDemandeValeurs(null);
            }
        }
        if (paramDem.getCorrespondanceParametreCDParametreDemandes().size() != 0) {
            for (CorrespondanceParametresEntity corresParam : paramDem.getCorrespondanceParametreCDParametreDemandes()) {
                entityManager.remove(corresParam);
            }
        }
        if (paramDem.getCPParametresDemandeCDParametreDemande().size() != 0) {
            for (CPParametreDemandeEntity CPParamDem : paramDem.getCPParametresDemandeCDParametreDemande()) {
                CPParamDem.setCDParametresDemandeCPParametreDemande(null);
            }
        }
        if (paramDem.getCDAttributsParametreDemandeCDParametreDemande().size() != 0) {
            for (CDAttributsParametreDemandeEntity attribut : paramDem.getCDAttributsParametreDemandeCDParametreDemande()) {
                entityManager.remove(attribut);
            }
        }
        entityManager.remove(paramDem);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public int ajouterCDParametreDemandeValeurs(int idCDParametreDemande, String CDParametreDemandeValeurs) {
        CDParametreDemandeValeursEntity paramDemVal = new CDParametreDemandeValeursEntity();
        paramDemVal.setValeurCDParametreDemandeValeurs(CDParametreDemandeValeurs);
        Query q = entityManager.createQuery("from CDParametreDemandeEntity pd where pd.identifiantParametreDemande=:idCDParametreDemande");
        q.setParameter("idCDParametreDemande", idCDParametreDemande);
        CDParametreDemandeEntity paramDem = (CDParametreDemandeEntity) q.getSingleResult();
        paramDem.getCDParametresDemandeValeursCDParametreDemande().add(paramDemVal);
        paramDemVal.setCDParametreDemandeCDParametreDemandeValeurs(paramDem);
        entityManager.persist(paramDem);
        return paramDemVal.getIdentifiantCDParametreDemandeValeurs();
    }

    /**
     * {@inheritDoc}
     */
    @SuppressWarnings("unchecked")
    @Override
    public Collection<CDParametreDemandeValeursEntity> recupererCDParametresDemandeValeurs(int idCDParametreDemande) {
        Query q =
                entityManager
                        .createQuery("Select CDParametresDemandeValeursCDParametreDemande from CDParametreDemandeEntity pd where pd.identifiantParametreDemande=:idCDParametreDemande");
        q.setParameter("idCDParametreDemande", idCDParametreDemande);
        return q.getResultList();
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void supprimerCDParametreDemandeValeurs(int idCDParametreDemandeValeurs) {
        Query q =
                entityManager
                        .createQuery("from CDParametreDemandeValeursEntity pda where pda.identifiantCDParametreDemandeValeurs=:idCDParametreDemandeValeurs");
        q.setParameter("idCDParametreDemandeValeurs", idCDParametreDemandeValeurs);
        CDParametreDemandeValeursEntity paramDemVal = (CDParametreDemandeValeursEntity) q.getSingleResult();
        entityManager.remove(paramDemVal);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public int ajouterCDParametreEtape(int idCDEtape, String CDParametreEtape, String typeSaisieCDParametreEtape, String descriptionCDParametreEtape,
            String valeurFixeCDParametreEtape) {
        CDParametreEtapeEntity paramEtape = new CDParametreEtapeEntity();
        paramEtape.setNomCDParametreEtape(CDParametreEtape);
        paramEtape.setDescriptionCDParametreEtape(descriptionCDParametreEtape);
        Query q = entityManager.createQuery("from CDEtapeEntity e where e.identifiantCDEtape=:idCDEtape");
        q.setParameter("idCDEtape", idCDEtape);
        CDEtapeEntity etape = (CDEtapeEntity) q.getSingleResult();
        etape.getCDParametresEtapeCDEtape().add(paramEtape);
        paramEtape.getCDEtapesCDParametreEtape().add(etape);
        entityManager.persist(paramEtape);
        if (typeSaisieCDParametreEtape.equals("Utilisateur")) {
            paramEtape.setTypeSaisieCDParametreEtape("user");
        } else {
            paramEtape.setTypeSaisieCDParametreEtape("admin");
            CorrespondanceParametresEntity corParam = new CorrespondanceParametresEntity();
            corParam.setCDParametresEtapeCorrespondanceParametre(entityManager.find(CDParametreEtapeEntity.class, paramEtape.getIdentifiantCDParametreEtape()));
            Map<CDEtapeEntity, String> corParamAdmin = corParam.getValeurCDParametreEtape();
            corParamAdmin.put(entityManager.find(CDEtapeEntity.class, idCDEtape), valeurFixeCDParametreEtape);
            corParam.setValeurCDParametreEtape(corParamAdmin);
            entityManager.persist(corParam);
        }
        entityManager.merge(paramEtape);
        return paramEtape.getIdentifiantCDParametreEtape();
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public String ajouterSelectionCDParametreEtape(int idCDEtape, int idCDParametreEtape, String valeurFixeCDParametreEtape) {
        Query q = entityManager.createQuery("from CDParametreEtapeEntity pe where pe.identifiantCDParametreEtape=:idCDParametreEtape");
        q.setParameter("idCDParametreEtape", idCDParametreEtape);
        CDParametreEtapeEntity paramEtape = (CDParametreEtapeEntity) q.getSingleResult();
        Query q2 = entityManager.createQuery("from CDEtapeEntity e where e.identifiantCDEtape=:idCDEtape");
        q2.setParameter("idCDEtape", idCDEtape);
        CDEtapeEntity etape = (CDEtapeEntity) q2.getSingleResult();
        etape.getCDParametresEtapeCDEtape().add(paramEtape);
        paramEtape.getCDEtapesCDParametreEtape().add(etape);
        if (paramEtape.getTypeSaisieCDParametreEtape().equals("admin")) {
            CorrespondanceParametresEntity corParam = new CorrespondanceParametresEntity();
            corParam.setCDParametresEtapeCorrespondanceParametre(entityManager.find(CDParametreEtapeEntity.class, paramEtape.getIdentifiantCDParametreEtape()));
            Map<CDEtapeEntity, String> corParamAdmin = corParam.getValeurCDParametreEtape();
            corParamAdmin.put(entityManager.find(CDEtapeEntity.class, idCDEtape), valeurFixeCDParametreEtape);
            corParam.setValeurCDParametreEtape(corParamAdmin);
            entityManager.persist(corParam);
        }
        entityManager.merge(paramEtape);
        return paramEtape.getNomCDParametreEtape();
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public String testerTypeSaisieCDParametreEtape(int idCDParametreEtape) {
        return entityManager.find(CDParametreEtapeEntity.class, idCDParametreEtape).getTypeSaisieCDParametreEtape();
    }

    /**
     * {@inheritDoc}
     */
    @SuppressWarnings("unchecked")
    @Override
    public Collection<CDParametreEtapeEntity> recupererCDParametresEtape(int idCDEtape) {
        Query q = entityManager.createQuery("Select CDParametresEtapeCDEtape from CDEtapeEntity e where e.identifiantCDEtape=:idCDEtape");
        q.setParameter("idCDEtape", idCDEtape);
        return q.getResultList();
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public CDParametreEtapeEntity recupererCDParametreEtape(int idCDParametreEtape) {
        Query q = entityManager.createQuery("from CDParametreEtapeEntity pe where pe.identifiantCDParametreEtape=:idCDParametreEtape");
        q.setParameter("idCDParametreEtape", idCDParametreEtape);
        return (CDParametreEtapeEntity) q.getSingleResult();
    }

    public String recupererValeurCDParametreEtape(int idCDEtape, int idCDParametreEtape) {
        Query q =
                entityManager
                        .createQuery("from CorrespondanceParametresEntity corParam where corParam.CDParametresEtapeCorrespondanceParametre=:idCDParametreEtape");
        q.setParameter("idCDParametreEtape", entityManager.find(CDParametreEtapeEntity.class, idCDParametreEtape));
        Collection<CorrespondanceParametresEntity> colCorrespondanceParametres = q.getResultList();
        for (CorrespondanceParametresEntity corParam : colCorrespondanceParametres) {
            if (corParam.getValeurCDParametreEtape().containsKey(entityManager.find(CDEtapeEntity.class, idCDEtape))) {
                return corParam.getValeurCDParametreEtape().get(entityManager.find(CDEtapeEntity.class, idCDEtape));
            }
        }
        return "";
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public Collection<CDParametreEtapeEntity> recupererTousCDParametresEtape(int idCDEtape) {
        Query q = entityManager.createQuery("Select CDParametresEtapeCDEtape from CDEtapeEntity e where e.identifiantCDEtape=:idCDEtape");
        q.setParameter("idCDEtape", idCDEtape);
        Collection<CDParametreEtapeEntity> colParametresEtape = q.getResultList();
        if (colParametresEtape.size() == 0) {
            Query q2 = entityManager.createQuery("From CDParametreEtapeEntity");
            return q2.getResultList();
        } else {
            Query q2 = entityManager.createQuery("From CDParametreEtapeEntity pe where pe not in (:colParametresEtape)");
            q2.setParameter("colParametresEtape", colParametresEtape);
            return q2.getResultList();
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void modifierCDParametreEtape(int idCDEtape, int idCDParametreEtape, String descriptionCDParametreEtape, String valeurFixeCDParametreEtape) {
        Query q = entityManager.createQuery("from CDParametreEtapeEntity pe where pe.identifiantCDParametreEtape=:idCDParametreEtape");
        q.setParameter("idCDParametreEtape", idCDParametreEtape);
        CDParametreEtapeEntity paramEtape = (CDParametreEtapeEntity) q.getSingleResult();
        paramEtape.setDescriptionCDParametreEtape(descriptionCDParametreEtape);
        Query q2 =
                entityManager
                        .createQuery("from CorrespondanceParametresEntity corParam where corParam.CDParametresEtapeCorrespondanceParametre=:idCDParametreEtape");
        q2.setParameter("idCDParametreEtape", entityManager.find(CDParametreEtapeEntity.class, idCDParametreEtape));
        Collection<CorrespondanceParametresEntity> colCorrespondanceParametres = q2.getResultList();
        for (CorrespondanceParametresEntity corParam : colCorrespondanceParametres) {
            if (corParam.getValeurCDParametreEtape().containsKey(entityManager.find(CDEtapeEntity.class, idCDEtape))) {
                if (!corParam.getValeurCDParametreEtape().containsValue(valeurFixeCDParametreEtape)) {
                    Map<CDEtapeEntity, String> corParamAdmin = corParam.getValeurCDParametreEtape();
                    corParamAdmin.remove(idCDEtape);
                    corParamAdmin.put(entityManager.find(CDEtapeEntity.class, idCDEtape), valeurFixeCDParametreEtape);
                    corParam.setValeurCDParametreEtape(corParamAdmin);
                    entityManager.merge(corParam);
                }
            }
        }
        entityManager.merge(paramEtape);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void supprimerCDParametreEtape(int idCDParametreEtape, int idCDEtape) {
        Query q = entityManager.createQuery("from CDParametreEtapeEntity pe where pe.identifiantCDParametreEtape=:idCDParametreEtape");
        q.setParameter("idCDParametreEtape", idCDParametreEtape);
        CDParametreEtapeEntity paramEtape = (CDParametreEtapeEntity) q.getSingleResult();
        Query q2 = entityManager.createQuery("from CDEtapeEntity e where e.identifiantCDEtape=:idCDEtape");
        q2.setParameter("idCDEtape", idCDEtape);
        CDEtapeEntity etape = (CDEtapeEntity) q2.getSingleResult();
        if (paramEtape.getTypeSaisieCDParametreEtape().equals("admin")) {
            Query q3 =
                    entityManager
                            .createQuery("from CorrespondanceParametresEntity corParam where corParam.CDParametresEtapeCorrespondanceParametre=:idCDParametreEtape");
            q3.setParameter("idCDParametreEtape", entityManager.find(CDParametreEtapeEntity.class, idCDParametreEtape));
            Collection<CorrespondanceParametresEntity> colCorrespondanceParametres = q3.getResultList();
            for (CorrespondanceParametresEntity corParam : colCorrespondanceParametres) {
                if (corParam.getValeurCDParametreEtape().containsKey(entityManager.find(CDEtapeEntity.class, idCDEtape))) {
                    entityManager.remove(corParam);
                }
            }
        }
        etape.getCDParametresEtapeCDEtape().remove(paramEtape);
        paramEtape.getCDEtapesCDParametreEtape().remove(etape);
        entityManager.merge(paramEtape);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void supprimerDefinitivementCDParametreEtape(int idCDParametreEtape) {
        Query q = entityManager.createQuery("from CDParametreEtapeEntity pe where pe.identifiantCDParametreEtape=:idCDParametreEtape");
        q.setParameter("idCDParametreEtape", idCDParametreEtape);
        CDParametreEtapeEntity paramEtape = (CDParametreEtapeEntity) q.getSingleResult();
        if (paramEtape.getCDEtapesCDParametreEtape().size() != 0) {
            for (CDEtapeEntity etape : paramEtape.getCDEtapesCDParametreEtape()) {
                etape.getCDParametresEtapeCDEtape().remove(paramEtape);
            }
        }
        if (paramEtape.getCorrespondanceParametreCDParametreEtapes().size() != 0) {
            for (CorrespondanceParametresEntity corresParam : paramEtape.getCorrespondanceParametreCDParametreEtapes()) {
                entityManager.remove(corresParam);
            }
        }
        if (paramEtape.getCPParametreEtapes().size() != 0) {
            for (CPParametreEtapeEntity CPParamEtape : paramEtape.getCPParametreEtapes()) {
                CPParamEtape.setCDParametreEtape(null);
            }
        }
        entityManager.remove(paramEtape);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public int ajouterCPParametreEtape(int idCDParametreEtape, String CPParametreEtape) {
        Query q = entityManager.createQuery("from CDParametreEtapeEntity pe where pe.identifiantCDParametreEtape=:idCDParametreEtape");
        q.setParameter("idCDParametreEtape", idCDParametreEtape);
        CDParametreEtapeEntity paramEtape = (CDParametreEtapeEntity) q.getSingleResult();
        CPParametreEtapeEntity CPParamEtape = new CPParametreEtapeEntity();
        CPParamEtape.setValeurCPParametreEtape(CPParametreEtape);
        paramEtape.getCPParametreEtapes().add(CPParamEtape);
        CPParamEtape.setCDParametreEtape(paramEtape);
        entityManager.persist(paramEtape);
        return CPParamEtape.getIdentifiantCPParametreEtape();
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public Collection<CPParametreEtapeEntity> recupererCPParametresEtape(int idCDParametreEtape) {
        Query q = entityManager.createQuery("select CPParametreEtapes from CDParametreEtapeEntity pe where pe.identifiantCDParametreEtape=:idCDParametreEtape");
        q.setParameter("idCDParametreEtape", idCDParametreEtape);
        return q.getResultList();
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void supprimerCPParametreEtape(int idCPParametreEtape) {
        Query q = entityManager.createQuery("from CPParametreEtapeEntity cppe where cppe.identifiantCPParametreEtape=:idCPParametreEtape");
        q.setParameter("idCPParametreEtape", idCPParametreEtape);
        CPParametreEtapeEntity CPParamEtape = (CPParametreEtapeEntity) q.getSingleResult();
        entityManager.remove(CPParamEtape);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public int ajouterCorrespondanceParametres(int idCorrespondanceParametresDemande, int idCorrespondanceParametresEtape) {
        Query q = entityManager.createQuery("from CDParametreDemandeEntity pd where pd.identifiantParametreDemande=:idCorrespondanceParametresDemande");
        q.setParameter("idCorrespondanceParametresDemande", idCorrespondanceParametresDemande);
        CDParametreDemandeEntity paramDem = (CDParametreDemandeEntity) q.getSingleResult();
        Query q2 = entityManager.createQuery("from CDParametreEtapeEntity pe where pe.identifiantCDParametreEtape=:idCorrespondanceParametresEtape");
        q2.setParameter("idCorrespondanceParametresEtape", idCorrespondanceParametresEtape);
        CDParametreEtapeEntity paramEtape = (CDParametreEtapeEntity) q2.getSingleResult();
        CorrespondanceParametresEntity corParam = new CorrespondanceParametresEntity();
        corParam.setCDParametresDemandeCorrespondanceParametre(paramDem);
        corParam.setCDParametresEtapeCorrespondanceParametre(paramEtape);
        paramDem.getCorrespondanceParametreCDParametreDemandes().add(corParam);
        paramEtape.getCorrespondanceParametreCDParametreEtapes().add(corParam);
        entityManager.persist(corParam);
        return corParam.getIdentifiantCorrespondanceParametres();
    }

    /**
     * {@inheritDoc}
     */
    @SuppressWarnings("unchecked")
    @Override
    public Collection<CorrespondanceParametresEntity> recupererCorrespondancesParametres(int idCDDemande, int idCDEtape) {
        Query q = entityManager.createQuery("from CorrespondanceParametresEntity");
        Collection<CorrespondanceParametresEntity> correspondances = q.getResultList();
        CDDemandeEntity demande = entityManager.find(CDDemandeEntity.class, idCDDemande);
        CDEtapeEntity etape = entityManager.find(CDEtapeEntity.class, idCDEtape);
        Collection<CorrespondanceParametresEntity> correspondancesARetourner = new HashSet<CorrespondanceParametresEntity>();
        for (CorrespondanceParametresEntity corparam : correspondances) {
            for (CDParametreDemandeEntity pd : demande.getCDParametresDemandeCDDemande()) {
                if (pd.getCorrespondanceParametreCDParametreDemandes().contains(corparam)) {
                    for (CDParametreEtapeEntity pe : etape.getCDParametresEtapeCDEtape()) {
                        if (pe.getCorrespondanceParametreCDParametreEtapes().contains(corparam)) {
                            correspondancesARetourner.add(corparam);
                        }
                    }
                }
            }
        }
        return correspondancesARetourner;
    }

    /**
     * {@inheritDoc}
     */
    @SuppressWarnings("unchecked")
    @Override
    public Collection<CDParametreDemandeEntity> recupererCorrespondancesParametresDemande(int idCDDemande) {
        Query q = entityManager.createQuery("Select CDParametresDemandeCorrespondanceParametre from CorrespondanceParametresEntity");
        Collection<CDParametreDemandeEntity> colParametresDemande = q.getResultList();
        if (colParametresDemande.size() == 0) {
            Query q2 = entityManager.createQuery("Select CDParametresDemandeCDDemande From CDDemandeEntity de where de.identifiantCDDemande=:idCDDemande");
            q2.setParameter("idCDDemande", idCDDemande);
            return q2.getResultList();
        } else {
            Query q2 = entityManager.createQuery("From CDParametreDemandeEntity pe where pe not in (:colParametresDemande)");
            q2.setParameter("colParametresDemande", colParametresDemande);
            Collection<CDParametreDemandeEntity> colParametresDemande2 = q2.getResultList();
            Collection<CDParametreDemandeEntity> colParametresDemande3 = new HashSet<CDParametreDemandeEntity>();
            for (CDParametreDemandeEntity pd : colParametresDemande2) {
                if (pd.getCDDemandesCDParametreDemande().contains(entityManager.find(CDDemandeEntity.class, idCDDemande))) {
                    colParametresDemande3.add(pd);
                }
            }
            return colParametresDemande3;
        }
    }

    /**
     * {@inheritDoc}
     */
    @SuppressWarnings("unchecked")
    @Override
    public Collection<CDParametreEtapeEntity> recupererCorrespondancesParametresEtape(int idCDEtape) {
        Query q = entityManager.createQuery("Select CDParametresEtapeCorrespondanceParametre from CorrespondanceParametresEntity");
        Collection<CDParametreEtapeEntity> colParametresEtape = q.getResultList();
        if (colParametresEtape.size() == 0) {
            Query q2 = entityManager.createQuery("Select CDParametresEtapeCDEtape From CDEtapeEntity e where e.identifiantCDEtape=:idCDEtape");
            q2.setParameter("idCDEtape", idCDEtape);
            return q2.getResultList();
        } else {
            Query q2 =
                    entityManager.createQuery("From CDParametreEtapeEntity pe where pe not in (:colParametresEtape) and pe.typeSaisieCDParametreEtape=:user");
            q2.setParameter("colParametresEtape", colParametresEtape);
            q2.setParameter("user", "user");
            Collection<CDParametreEtapeEntity> colParametresEtape2 = q2.getResultList();
            Collection<CDParametreEtapeEntity> colParametresEtape3 = new HashSet<CDParametreEtapeEntity>();
            for (CDParametreEtapeEntity pe : colParametresEtape2) {
                if (pe.getCDEtapesCDParametreEtape().contains(entityManager.find(CDEtapeEntity.class, idCDEtape))) {
                    colParametresEtape3.add(pe);
                }
            }
            return colParametresEtape3;
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void supprimerCorrespondanceParametres(int idCorrespondanceParametres) {
        Query q = entityManager.createQuery("from CorrespondanceParametresEntity cp where cp.identifiantCorrespondanceParametres=:idCorrespondanceParametres");
        q.setParameter("idCorrespondanceParametres", idCorrespondanceParametres);
        CorrespondanceParametresEntity corParam = (CorrespondanceParametresEntity) q.getSingleResult();
        entityManager.remove(corParam);
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public Collection<String> recupererEtat() {
        Query query = entityManager.createQuery("Select nomEtat from EtatEntity");
        return (Collection<String>) query.getResultList();
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public Collection<String> recupererType() {
        Query query = entityManager.createQuery("Select nomTypeDemande from TypeDemandeEntity");
        return (Collection<String>) query.getResultList();
    }

    @Override
    public Collection<CDDemandeEntity> recupererNomDemandeAdmin() {
        Query query = entityManager.createQuery("Select nomDemandeAdmin from CDDemandeEntity");
        return (Collection<CDDemandeEntity>) query.getResultList();
    }

    @Override
    public Collection<CDDemandeEntity> recupererNombreDemandeAdmin() {
        Query query = entityManager.createQuery("Select Count(*) from CDDemandeEntity");
        return (Collection<CDDemandeEntity>) query.getResultList();
    }

    @Override
    public Collection<CPDemandeEntity> recupererDemande() {
        // Query query
        // =entityManager.createQuery("Select Count(*) from CDDemandeEntity where nomDemandeAdmin = (Select demandeAdminDemande from DemandeEntity where etatDemande=(Select identifiantEtat from EtatEntity where nomEtat='resolu')) ");
        Query query = entityManager.createQuery("from CPDemandeEntity ");
        return (Collection<CPDemandeEntity>) query.getResultList();
    }

    @Override
    public Collection<CPDemandeEntity> recupererPersonneEffectueDemande() {
        Query query = entityManager.createQuery("Select per.ldapPersonne from DemandeEntity d left join d.personneEffectueDemande per ");
        return (Collection<CPDemandeEntity>) query.getResultList();
    }

    @Override
    public Collection<CategorieEntity> recupererNomCategorie() {
        Query query = entityManager.createQuery("Select nomCategorie from CategorieEntity");
        return (Collection<CategorieEntity>) query.getResultList();
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public Collection<CPDemandeEntity> recupererDemandesEnCoursEnAttente() {

        Query query = entityManager.createQuery("from CPDemandeEntity d left join fetch d.etatCPDemande e where e.nomEtat=:enCours OR  e.nomEtat=:enAttente ");
        query.setParameter("enCours", "En cours de traitement");
        query.setParameter("enAttente", "En attente");

        return (Collection<CPDemandeEntity>) query.getResultList();
    }

    public Collection<String> recupererTypesDemandeNom() {
        Query q = entityManager.createQuery("Select nomTypeDemande from TypeDemandeEntity");
        return (Collection<String>) q.getResultList();
    }

    public Collection<CPDemandeEntity> recupererDemandeOuvert() {
        Query q = entityManager.createQuery("SELECT d from CPDemandeEntity d left join fetch d.etatCPDemande e Where e.nomEtat = :param Order By d.dateCPDemande Desc");
        q.setParameter("param", "Ouverte");
        return (Collection<CPDemandeEntity>) q.getResultList();
    }
    
    public Collection<CPDemandeEntity> recupererDemandeReOuvert() {
        Query q = entityManager.createQuery("SELECT d from CPDemandeEntity d left join fetch d.etatCPDemande e Where e.nomEtat = :param Order By d.dateCPDemande Desc");
        q.setParameter("param", "Ré-ouverte");
        return (Collection<CPDemandeEntity>) q.getResultList();
    }
    
    public Collection<CPDemandeEntity> recupererDemandeEnCours() {
        Query q = entityManager.createQuery("SELECT d from CPDemandeEntity d left join fetch d.etatCPDemande e Where e.nomEtat = :param Order By d.dateCPDemande Desc");
        q.setParameter("param", "En cours de traitement");
        return (Collection<CPDemandeEntity>) q.getResultList();
    }
    
    public Collection<CPDemandeEntity> recupererDemandeEnAttente() {
        Query q = entityManager.createQuery("SELECT d from CPDemandeEntity d left join fetch d.etatCPDemande e Where e.nomEtat = :param Order By d.dateCPDemande Desc");
        q.setParameter("param", "En attente");
        return (Collection<CPDemandeEntity>) q.getResultList();
    }
    
    public Collection<CommentairesEntity> recupererCommentairesDemande(int idDemande) {
        Query q = entityManager.createQuery("SELECT c from CommentairesEntity c left join fetch c.CPDemandeCommentaire d Where d.identifiantCPDemande = :param");
        q.setParameter("param", idDemande);
        return (Collection<CommentairesEntity>) q.getResultList();
    }
    
    public Collection<CommentairesEntity> recupererCommentaires() {
        Query q = entityManager.createQuery("from CommentairesEntity c");
        return (Collection<CommentairesEntity>) q.getResultList();
    }

    public CommentairesEntity recupererCommentairesDemandeByID(int idCommentaire) {
    	System.out.println("DAO : "+idCommentaire);
        Query q = entityManager.createQuery("from CommentairesEntity c Where c.identifiantCommentaire =:param");
        q.setParameter("param", idCommentaire);
        CommentairesEntity com =  new CommentairesEntity();
        com = (CommentairesEntity) q.getSingleResult();
        CPDemandeEntity d = com.getCPDemandeCommentaire();
        
        return com;
    }
    
    public void ajouterUnCommentaire(String commentaire, PersonneEntity auteur, CPDemandeEntity demandeConcerne) {
    	CommentairesEntity com = new CommentairesEntity();
    	com.setCommentaire(commentaire);
    	com.setDateCommentaire(new Date());
    	com.setPersonneCommentaire(auteur);
    	com.setCPDemandeCommentaire(demandeConcerne);
    	entityManager.persist(com);
    }
    
    public void supprimerCommentaire(int idCommentaire) {
        Query q = entityManager.createQuery("Delete CommentairesEntity where identifiantCommentaire=:param");
        q.setParameter("param", idCommentaire);
        q.executeUpdate();
    }
    
    /**
     * {@inheritDoc}
     */
    @Override
    public Collection<CPDemandeEntity> rechercherDemandesParTypeParEtat(String etat, String type) {

        StringBuilder requete = new StringBuilder();
        requete.append(" SELECT d from CPDemandeEntity d");
        requete.append(" left join fetch d.etatCPDemande e ");
        requete.append(" left join fetch d.CDDemandeCPDemande da ");
        requete.append(" left join fetch da.typeDemandeCDDemande tda ");
        requete.append("where 1=1 ");
        Map<String, Object> mapParams = new HashMap<String, Object>();

        if (etat != null && !etat.equals("Tous")) {

            String param = "etatDem";
            requete.append("AND e.nomEtat = :" + param);
            mapParams.put(param, etat);

        }
        if (type != null && !type.equals("Tous")) {

            String param = "typeDem";
            requete.append(" AND tda.nomTypeDemande = :" + param);
            mapParams.put(param, type);

        }
        
        requete.append(" ORDER BY d.dateCPDemande DESC");

        String string = requete.toString();
        Query query = entityManager.createQuery(string);

        // Mise en place des restrictions
        Set<Entry<String, Object>> mapSet = mapParams.entrySet();
        for (Entry<String, Object> entry : mapSet) {
            query.setParameter(entry.getKey(), entry.getValue());
        }

        // Query query = entityManager.createQuery("from DemandeEntity");
        return (Collection<CPDemandeEntity>) query.setMaxResults(40).getResultList();

    }

    @Override
    public void modifierEtatDemande(int idCPDemande, String nomEtat) {
        Query q = entityManager.createQuery("from CPDemandeEntity cpDem where cpDem.identifiantCPDemande=:idCPDemande");
        q.setParameter("idCPDemande", idCPDemande);
        CPDemandeEntity demande = new CPDemandeEntity();
        demande = (CPDemandeEntity) q.getSingleResult();
        EtatEntity etat = entityManager.find(EtatEntity.class, nomEtat);
        demande.setEtatCPDemande(etat);
        entityManager.merge(etat);

    }

    public Collection<String> recupererUtilisateursNom() {
        Query query = entityManager.createQuery("Select ldapPersonne from PersonneEntity");
        return (Collection<String>) query.getResultList();

    }

    public Collection<CPPointDeControleEntity> recupererCPPointControleDemande2(int idDemande) {

        StringBuilder requete = new StringBuilder();
        Map<String, Object> mapParams = new HashMap<String, Object>();
        requete.append(" SELECT a from CPPointDeControleEntity a");
        requete.append(" left join fetch a.CDPointControleCPPointControle p");
        requete.append("  left join fetch a.CPdemandeCPPointControle d ");
        String param = "param";
        requete.append(" Where  d.identifiantCPDemande = :" + param);
        mapParams.put(param, idDemande);

        String string = requete.toString();
        Query query = entityManager.createQuery(string);

        Set<Entry<String, Object>> mapSet = mapParams.entrySet();
        for (Entry<String, Object> entry : mapSet) {
            query.setParameter(entry.getKey(), entry.getValue());
        }

        return (Collection<CPPointDeControleEntity>) query.getResultList();
    }
    
    public Collection<CPPointDeControleEntity> recupererCPPointControleDemande(int idDemande) {

        StringBuilder requete = new StringBuilder();
        Map<String, Object> mapParams = new HashMap<String, Object>();
        requete.append(" SELECT a from CPPointDeControleEntity a");
        requete.append(" left join fetch a.CDPointControleCPPointControle p");
        requete.append("  left join fetch  p.CDDemandesCDPointControle da ");
        requete.append("  left join  da.CPDemandesCDDemande d ");
        String param = "param";
        requete.append(" Where  d.identifiantCPDemande = :" + param);
        mapParams.put(param, idDemande);

        String string = requete.toString();
        Query query = entityManager.createQuery(string);

        Set<Entry<String, Object>> mapSet = mapParams.entrySet();
        for (Entry<String, Object> entry : mapSet) {
            query.setParameter(entry.getKey(), entry.getValue());
        }

        return (Collection<CPPointDeControleEntity>) query.getResultList();
    }

    @Override
    public void modifierValeurPointControle(int idPointControle, String nouvelleValeur) {
        Query q = entityManager.createQuery("from CPPointDeControleEntity p where p.identifiantCPPointDeControle=:idPointControle");
        q.setParameter("idPointControle", idPointControle);
        CPPointDeControleEntity pointCtrl = (CPPointDeControleEntity) q.getSingleResult();
        pointCtrl.setValeurPointDeControle(nouvelleValeur);
        entityManager.merge(pointCtrl);

    }
    
    /*
    public void modifierValeurPointControle2(int idPointDeControle, int idLaDemande, String nouvelleValeur) {
    	StringBuilder requete = new StringBuilder();
        Map<String, Object> mapParams = new HashMap<String, Object>();
        requete.append("from CorrespondanceCPDemandeCPPointControleEntity c ");
        requete.append("left join c.CPPointControleCorrespondanceCPDemandeCPPointControle pc ");
		requete.append("left join c.CPDemandeCorrespondanceCPDemandeCPPointControle d");
		String param = "idDemande";
		requete.append(" where d.identifiantCPDemande=:"+param);
		mapParams.put(param, idLaDemande);
		
		String param2 = "idPointControle";
		requete.append(" AND pc.identifiantCPPointDeControle=:"+param2);
		mapParams.put(param2, idPointDeControle);
		
		String string = requete.toString();
        Query query = entityManager.createQuery(string);

        Set<Entry<String, Object>> mapSet = mapParams.entrySet();
        for (Entry<String, Object> entry : mapSet) {
            query.setParameter(entry.getKey(), entry.getValue());
        }

		CorrespondanceCPDemandeCPPointControleEntity correspPointCtrl = (CorrespondanceCPDemandeCPPointControleEntity) query.getSingleResult();
		
		correspPointCtrl.setValeurPC(nouvelleValeur);
        entityManager.merge(correspPointCtrl);

    }*/

    public CPDemandeEntity recupererCPDemandeParId(int idCPDemande) {
        CPDemandeEntity demande = entityManager.find(CPDemandeEntity.class, idCPDemande);
        demande.getCDDemandeCPDemande().getCDEtapesCDDemande().size();
        for (CDEtapeEntity e : demande.getCDDemandeCPDemande().getCDEtapesCDDemande()) {
            e.getCDParametresEtapeCDEtape().size();
            for (CDParametreEtapeEntity cp : e.getCDParametresEtapeCDEtape()) {
                cp.getCPParametreEtapes().size();
            }
        }
        demande.getEtapesCPDemande().size();
        for (CPEtapeEntity e : demande.getEtapesCPDemande()) {
            e.getCPParametresEtapeCPEtape().size();
        }
        return demande;
    }

    public Collection<CPDemandeEntity> filtrerDemandesParTypeParEtatParPersonneParDate(String etat, String type, String personne, Date date1, Date date2) {

        StringBuilder requete = new StringBuilder();
        requete.append(" SELECT d from CPDemandeEntity d");
        requete.append(" left join fetch d.etatCPDemande e ");
        requete.append(" left join fetch d.CDDemandeCPDemande da ");
        requete.append(" left join fetch d.personneEffectueCPDemande dp ");
        requete.append(" left join fetch da.typeDemandeCDDemande tda ");
        requete.append("where 1=1 ");
        Map<String, Object> mapParams = new HashMap<String, Object>();

        if (etat != null && !etat.equals("Aucun choix")) {

            String param = "etatDem";
            requete.append("AND e.nomEtat = :" + param);
            mapParams.put(param, etat);

        }
        if (type != null && !type.equals("Aucun choix")) {

            String param = "typeDem";
            requete.append(" AND tda.nomTypeDemande = :" + param);
            mapParams.put(param, type);

        }
        if (personne != null && !personne.equals("Aucun choix")) {

            String param = "perDem";
            requete.append(" AND dp.ldapPersonne = :" + param);
            mapParams.put(param, personne);

        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        if (date1 != null) {
        	Calendar c = Calendar.getInstance();
            c.setTime(date1);
           // c.add(Calendar.DATE, 1);
            
            String d = sdf.format(date1);
            String param = "date1Dem";

            requete.append(" AND d.dateCPDemande >= :" + param);
            try {
                System.out.println(d);
                mapParams.put(param, sdf.parse(d));
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }

        if (date2 != null) {
            Calendar c = Calendar.getInstance();
            c.setTime(date2);
            c.add(Calendar.DATE, 1);
            String d = sdf.format(c.getTime());
            String param = "date2Dem";
            requete.append(" AND d.dateCPDemande <= :" + param);
            try {
                mapParams.put(param, sdf.parse(d));
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }
        
        requete.append(" Order By d.dateCPDemande Desc");
        String string = requete.toString();
        Query query = entityManager.createQuery(string);

        Set<Entry<String, Object>> mapSet = mapParams.entrySet();
        for (Entry<String, Object> entry : mapSet) {
            query.setParameter(entry.getKey(), entry.getValue());
        }

        Collection<CPDemandeEntity> results = query.setMaxResults(20).getResultList();
        for (CPDemandeEntity c : results) {
            c.getParametreDemandeCPDemandes().size();
        }
        return results;

    }
    
    public Collection<CPDemandeEntity> filtrerDemandesParTypeParPersonneParDate3Close(String etat, String type, String personne, Date date1, Date date2) {

        StringBuilder requete = new StringBuilder();
        requete.append(" SELECT d from CPDemandeEntity d");
        requete.append(" left join fetch d.etatCPDemande e ");
        requete.append(" left join fetch d.CDDemandeCPDemande da ");
        requete.append(" left join fetch d.personneEffectueCPDemande dp ");
        requete.append(" left join fetch da.typeDemandeCDDemande tda ");
        requete.append("where 1=1 ");
        Map<String, Object> mapParams = new HashMap<String, Object>();

        if (etat != null && !etat.equals("Aucun choix")) {

            String param = "etatDem";
            requete.append("AND e.nomEtat = :" + param);
            mapParams.put(param, etat);

        }
        if (personne != null && !personne.equals("Aucun choix")) {

            String param = "perDem";
            requete.append(" AND dp.ldapPersonne = :" + param);
            mapParams.put(param, personne);

        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        if (date1 != null) {

            String d = sdf.format(date1);
            String param = "date1Dem";

            requete.append(" AND d.dateCPDemande >= :" + param);
            try {
                System.out.println(d);
                mapParams.put(param, sdf.parse(d));
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }

        if (date2 != null) {
            Calendar c = Calendar.getInstance();
            c.setTime(date2);
            c.add(Calendar.DATE, 1);
            String d = sdf.format(c.getTime());
            String param = "date2Dem";
            requete.append(" AND d.dateCPDemande <= :" + param);
            try {
                mapParams.put(param, sdf.parse(d));
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }

        String string = requete.toString();
        Query query = entityManager.createQuery(string);

        Set<Entry<String, Object>> mapSet = mapParams.entrySet();
        for (Entry<String, Object> entry : mapSet) {
            query.setParameter(entry.getKey(), entry.getValue());
        }

        Collection<CPDemandeEntity> results = query.setMaxResults(3).getResultList();
        for (CPDemandeEntity c : results) {
            c.getParametreDemandeCPDemandes().size();
        }
        return results;

    }

    @Override
    public void supprimerDemande(int idCPDemande) {
    	CPDemandeEntity cpd = new CPDemandeEntity();
    	cpd = recupererCPDemandeById(idCPDemande);
    	
    	Query theQuery= entityManager.createQuery("Delete CPParametreEtapeEntity where CPdemandeCPParametreEtape=:idCPDemande");
    	theQuery.setParameter("idCPDemande", cpd);
    	theQuery.executeUpdate();
    	
    	Query query = entityManager.createQuery("Delete CPEtapeEntity where CPDemandeCPEtape=:idCPDemande");
    	query.setParameter("idCPDemande", cpd);
    	query.executeUpdate();
    	
    	/*Query disable = entityManager.createQuery("SET foreign_key_checks=0");
    	disable.executeUpdate();*/
    	Query quer = entityManager.createQuery("Delete CPPointDeControleEntity where CPdemandeCPPointControle=:idCPDemande");
    	quer.setParameter("idCPDemande", cpd);
    	quer.executeUpdate();
    	/*Query able = entityManager.createQuery("SET foreign_key_checks=1");
    	able.executeUpdate();*/
    	
    	Query que = entityManager.createQuery("Delete CPParametreDemandeEntity where CPdemandeCPParametreDemande=:idCPDemande");
    	que.setParameter("idCPDemande", cpd);
    	que.executeUpdate();
    	
    	Query qu = entityManager.createQuery("Delete CommentairesEntity where CPDemandeCommentaire=:idCPDemande");
    	qu.setParameter("idCPDemande", cpd);
    	qu.executeUpdate();
    	
        Query q = entityManager.createQuery("Delete CPDemandeEntity where identifiantCPDemande=:idCPDemande");
        q.setParameter("idCPDemande", idCPDemande);
        q.executeUpdate();
    }

    public List<CPDemandeEntity> rechercherDemandeParNom(String mot) {
        List<CPDemandeEntity> res = new ArrayList<CPDemandeEntity>();

        boolean trouve=false;
        Query query = entityManager.createQuery("from CPDemandeEntity Where titreCPDemande =:param");
        query.setParameter("param", mot);
        if (query.getResultList().size() > 0) {
            CPDemandeEntity cpdemande = (CPDemandeEntity) query.getSingleResult();
            cpdemande.getParametreDemandeCPDemandes().size();
            res.add(cpdemande);
            trouve=true;
        }

        int cpt=0;
        Collection<CPDemandeEntity> all = entityManager.createQuery("Select d from CPDemandeEntity d Order By d.dateCPDemande Desc").getResultList();
        for (CPDemandeEntity c : all) {
            if (c.getTitreCPDemande().toLowerCase().contains(mot.toLowerCase()) && cpt<20) {
                c.getParametreDemandeCPDemandes().size();
                res.add(c);
                cpt++;
            }
            
        }
        // quand il n'y a qu'un seul résultat, ça retourne le résultat en doublant
        if (cpt==1 && trouve==true) res.remove(1);

        return res;
    }

    public CPDemandeEntity recupererCPDemandeById(int idCPDemande) {

        Query q =
                entityManager
                        .createQuery(" SELECT d from CPDemandeEntity d left join fetch d.parametreDemandeCPDemandes p Where d.identifiantCPDemande=:idCPDemande");
        q.setParameter("idCPDemande", idCPDemande);

        return (CPDemandeEntity) q.getSingleResult();
    }

    public CPDemandeEntity recuperCPDemandeWithEtapes(int idCPDemande) {

        Query q =
                entityManager
                        .createQuery(" Select d from CPDemandeEntity d left join fetch d.etapesCPDemande e left join fetch e.CDEtapeCPEtape Where d.identifiantCPDemande=:idCPDemande");
        q.setParameter("idCPDemande", idCPDemande);
        return (CPDemandeEntity) q.getSingleResult();
    }

    public CPEtapeEntity recupererCPEtapeByid(int idCPEtape) {

        Query q = entityManager.createQuery(" Select e from CPEtapeEntity e Where e.identifiantCPEtape=:idCPEtape");
        q.setParameter("idCPEtape", idCPEtape);

        return (CPEtapeEntity) q.getSingleResult();
    }

    public void modifierEtatCPEtape(int idCPEtape, String nomEtat) {

        Query q = entityManager.createQuery("from CPEtapeEntity p where p.identifiantCPEtape=:identifiantCPEtape");
        q.setParameter("identifiantCPEtape", idCPEtape);
        CPEtapeEntity etape = new CPEtapeEntity();
        etape = (CPEtapeEntity) q.getSingleResult();

        EtatEntity etat = entityManager.find(EtatEntity.class, nomEtat);

        etape.setEtatCPEtape(etat);
        entityManager.merge(etape);

    }

    public EtatEntity recupererEtatByNomEtat(String nomEtat) {
        return entityManager.find(EtatEntity.class, nomEtat);
    }

    @Override
    public void modifierDateDernierEtatDemande(int idCPDemande) {
        Query q = entityManager.createQuery("from CPDemandeEntity cpDem where cpDem.identifiantCPDemande=:idCPDemande");
        q.setParameter("idCPDemande", idCPDemande);
        CPDemandeEntity demande = new CPDemandeEntity();
        demande = (CPDemandeEntity) q.getSingleResult();
        demande.setDateEtat(new Date());
        entityManager.merge(demande);
    }

    @Override
    public void ajouterEtat(String nomEtat) {
        EtatEntity etat = new EtatEntity();
        etat.setNomEtat(nomEtat);
        entityManager.persist(etat);
    }

    @Override
    public PersonneEntity recupererPersonneByLdap(String ldap) {

        Query q = entityManager.createQuery(" from PersonneEntity p Where p.ldapPersonne=:ldap");
        q.setParameter("ldap", ldap);

        return (PersonneEntity) q.getSingleResult();
    }

}
